﻿package DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import model.Category;
import model.Study;

public class StudyDao {

	public StudyDao() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	String url = "jdbc:mysql://localhost:3306/QUESTUDY";
	String user = "root";
	String pass = "webclass";

	//String pass = "webclass";

	// 선택한 스터디 상세정보 불러오기
	public Study getStudyInfo(int studyId) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Study study = null;
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select * from study,category,deposit,fine,user where cid=cateid and studyid=? and depositId=did and fineId=fid and userid=uid");
			pstmt.setInt(1, studyId);
			rs = pstmt.executeQuery();
			if (rs.next()) {

				int sid = rs.getInt("studyid");
				String title = rs.getString("title").trim();
				String content = rs.getString("content").trim();
				int uid = rs.getInt("uid");
				Timestamp createdTime = rs.getTimestamp("createdTime");
				Timestamp updatedTime = rs.getTimestamp("updatedTime");
				Timestamp expiredTime = rs.getTimestamp("expiredTime");
				int deposit = rs.getInt("deposit");
				int fine = rs.getInt("fine");
				int currentMember = rs.getInt("currentMember");
				int memberLimit = rs.getInt("memberLimit");
				String category = rs.getString("category");
				int currentDeposit = rs.getInt("currentDeposit");
				String memid = rs.getString("memid");
				study = new Study(sid, title, content, uid, createdTime,
						updatedTime, expiredTime, deposit, fine, currentMember,
						memberLimit, category, currentDeposit);
				study.setMemid(memid);
			}

			return study;

		} catch (Exception ex) {
			ex.printStackTrace();
			return study;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 스터디 전체 리스트 불러오기
	public ArrayList<Study> getAllStudyList() throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Study> study = new ArrayList<Study>();
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select studyid, title, memid, createdTime, category from study,category,user where cid=cateid and uid=userid order by studyId desc");
			rs = pstmt.executeQuery();
			while (rs.next()) {

				int sid = rs.getInt("studyid");
				String title = rs.getString("title").trim();
				String memid = rs.getString("memid");
				Timestamp createdTime = rs.getTimestamp("createdTime");
				String category = rs.getString("category");
				study.add(new Study(sid, title, memid, createdTime, category));
			}

			return study;

		} catch (Exception ex) {
			ex.printStackTrace();
			return study;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 분야(카테고리) 목록 가져오기
	public ArrayList<Category> getAllCategory() throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Category> categories = new ArrayList<Category>();
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con.prepareStatement("select * from category");
			rs = pstmt.executeQuery();
			while (rs.next()) {

				int cateid = rs.getInt("cateid");
				String category = rs.getString("category").trim();

				categories.add(new Category(cateid, category));
			}

			return categories;

		} catch (Exception ex) {
			ex.printStackTrace();
			return categories;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 분야로 검색한 스터디 리스트 가져오기
	public ArrayList<Study> getCategoryStudyList(int cid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Study> study = new ArrayList<Study>();
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select studyid, title, memid, createdTime, category from study,category,user where cid=cateid and uid=userid and cid=?");
			pstmt.setInt(1, cid);
			rs = pstmt.executeQuery();
			while (rs.next()) {

				int sid = rs.getInt("studyid");
				String title = rs.getString("title").trim();
				String memid = rs.getString("memid");
				Timestamp createdTime = rs.getTimestamp("createdTime");
				String category = rs.getString("category");
				study.add(new Study(sid, title, memid, createdTime, category));
			}

			return study;

		} catch (Exception ex) {
			ex.printStackTrace();
			return study;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 특정 단어로 검색한 스터디 리스트 불러오기
	public ArrayList<Study> getSearchStudyList(String keyword) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Study> study = new ArrayList<Study>();
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select studyid, title, memid, createdTime, category from study,category,user where title like ? and cid=cateid and uid=userid");
			pstmt.setString(1, "%" + keyword + "%");
			rs = pstmt.executeQuery();
			while (rs.next()) {

				int sid = rs.getInt("studyid");
				String title = rs.getString("title").trim();
				String memid = rs.getString("memid");
				Timestamp createdTime = rs.getTimestamp("createdTime");
				String category = rs.getString("category");
				study.add(new Study(sid, title, memid, createdTime, category));
			}

			return study;

		} catch (Exception ex) {
			ex.printStackTrace();
			return study;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 분야 + 키워드로 검색한 스터디 리스트 불러오기
	public ArrayList<Study> getDetailSearchStudyList(int cid, String keyword)
			throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Study> study = new ArrayList<Study>();
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select studyid, title, memid, createdTime, category from study,category,user where title like ? and cid=cateid and uid=userid and cid=?");
			pstmt.setString(1, "%" + keyword + "%");
			pstmt.setInt(2, cid);
			rs = pstmt.executeQuery();
			while (rs.next()) {

				int sid = rs.getInt("studyid");
				String title = rs.getString("title").trim();
				String memid = rs.getString("memid");
				Timestamp createdTime = rs.getTimestamp("createdTime");
				String category = rs.getString("category");
				study.add(new Study(sid, title, memid, createdTime, category));
			}

			return study;

		} catch (Exception ex) {
			ex.printStackTrace();
			return study;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 마감된 스터디 체크
	public boolean checkExpiredStudy(int sid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select studyId from study where expiredTime<sysdate() and studyId=?");
			pstmt.setInt(1, sid);
			rs = pstmt.executeQuery();
			if (rs.next()) {

				return true;
			} else {
				return false;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 스터디 생성하기
	public int insertStudyInfo(Study newStudy) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			// 보증금 값 불러오기
			DepositDao dao = new DepositDao();
			int depositValue = dao.getDepositValue(newStudy.getDid());

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("insert into study(title, content, uid, createdTime, expiredTime, did, fid, currentMember, memberLimit, cid, currentDeposit) values(?,?,?,sysdate(),?,?,?,1,?,?,?)");

			pstmt.setString(1, newStudy.getTitle());
			pstmt.setString(2, newStudy.getContent());
			pstmt.setInt(3, newStudy.getUid());
			pstmt.setTimestamp(4, newStudy.getExpiredTime());
			pstmt.setInt(5, newStudy.getDid());
			pstmt.setInt(6, newStudy.getFid());
			pstmt.setInt(7, newStudy.getMemberLimit());
			pstmt.setInt(8, newStudy.getCid());
			pstmt.setInt(9, depositValue);
			pstmt.executeUpdate();

			// 보증금 차감

			pstmt = con
					.prepareStatement("update user set point=point-? where userid=?");
			pstmt.setInt(1, depositValue);
			pstmt.setInt(2, newStudy.getUid());
			pstmt.executeUpdate();

			pstmt = con
					.prepareStatement("select studyid, uid from study where title=? and content=? and uid=?");
			pstmt.setString(1, newStudy.getTitle());
			pstmt.setString(2, newStudy.getContent());
			pstmt.setInt(3, newStudy.getUid());

			rs = pstmt.executeQuery();
			if (rs.next()) {

				int sid = rs.getInt("studyid");
				int uid = rs.getInt("uid");
				ParticipationDao dao2 = new ParticipationDao();
				dao2.insertStudyTeamLeader(sid, uid);
				StudyFineDao studyFineDao = new StudyFineDao();
				studyFineDao.insertStudyFineInfo(sid, uid);

				return rs.getInt("studyid");
			} else {
				return 0;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
			return 0;

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 내가 가입한 스터디
	public ArrayList<Study> getJoinedStudy(int userId) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Study> studyList = new ArrayList<Study>();
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select * from participation,study,category where isConfirmed=1 and cateid=cid and studyId=sid and participation.uid=?");

			pstmt.setInt(1, userId);

			rs = pstmt.executeQuery();

			while (rs.next()) {
				int sid = rs.getInt("studyid");
				String title = rs.getString("title").trim();
				String category = rs.getString("category");
				studyList.add(new Study(sid, title, category));
			}

			return studyList;

		} catch (Exception ex) {
			ex.printStackTrace();
			return studyList;

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}
}